/* Original */
libname tbrp "C:\Users\Public\Documents\donnees\TbRP\";
libname tbrpmin "C:\Users\Public\Documents\donnees\TbRP\mini";
libname result   'C:\Users\Public\Documents\Greg_JEG\ShiftShare';
libname table "C:\Users\Public\Documents\Greg_JEG\Tablesmodif\zepopemp";
libname ccom "C:\Users\Public\Documents\donnees\Code_commune";

options ls=80 mprint ;

/*************************************/
/* Instrument Shift Share Immigrants */
/*************************************/
/* 1968 */
Data table;
length depcom $5;
length d $2;
length c $3;
set 
tbrp.rp68 (keep = depcom s AD DIP IN PN CM1 TA CM1)
;
d=substr(depcom,1,2);
c=substr(depcom,3,3);
sond=4;
run;

Data table;
set table;
length cgeo $ 5;
/* create municipality code */
cgeo=cats(d,c);
drop c;
/* sexe */
if s=1 then homme=1;
else homme=0;
drop s;
/* age */
age=AD*1;
drop ad;
/* education */
/* 4 groupes: {CEPP,BEPC,etc...},{Niveau Bac:brevet pro,etc},{Bac},{Bac+}*/
if DIP='10' or DIP='11' then educ='CEPP';
if DIP='20' or DIP='21' or DIP='22' or DIP='23' or DIP='30' or DIP='31' then educ='BEPC';
if DIP='40' or DIP='41' or DIP='42' or DIP='43' or DIP='44' or DIP='45' then educ='NBAC';
if DIP='50' then educ='BACP';
drop dip;
/* immigrant status */
if IN=2 or IN=3 then immig=1;
else immig=0;
drop in;
/* people born in France are not considered immigrant */
if pn='**' or pn='91' or pn='92' or pn='93' or pn='94' or pn='95' or pn='96' or pn='97' or pn='98' then immig=0; 
/* living place in 1962 */
if cm1=6 then abd=1;
else abd=0;
drop cm1;
/* new immigrants */
if immig=1 and abd=1 then nimmig=1;
else nimmig=0;
/* LF statut */
/* actifs et non �tudiant */
if TA=1 then emploi=1;
else emploi=0;
if TA=1 or TA=3 then actif=1;
else actif=0;
if TA=5 or TA=2 then etud=1;
else etud=0;
if TA=6 then milit=1;
else milit=0;
if TA=3 then chom=1;
else chom=0;
drop ta;
run;

data table;
set table;
/* origins */
/* use the country of birth */
if not(pn='**') then pn2=input(pn,best4.);
/* europe */
if pn2=>1 and pn2<=29 then pnai='Europe';
/* dom tom */
/*if pn2=>91 and pn2<=98 then pnai="Europe";*/
/* Africa */
if pn2=>30 and pn2<=59 then pnai="Africa";
/* Asia */
if pn2=>71 and pn2<=85 then pnai="Asia";
/* Other */
/* Amerique */
if pn2=>60 and pn2<=69 then pnai="Other";
/* Oceanie */
if pn2=>86 and pn2<=89 then pnai="Other";
/* Unknown */
if pn2=99 then pnai="Other";
/* Countries */
if pn2=30 or pn2=31 then pnai="Maghreb";
if pn2=45 then pnai="Maghreb";
if pn2=52 then pnai="Maghreb";
pnaic=pnai;
length pnaid $ 15;
if pn='01'	 then pnaid="allemagne";
if pn='02'	 then pnaid="autriche";
if pn='03'	 then pnaid="belgique";
if pn='04'	 then pnaid="bulgarie";
if pn='05'	 then pnaid="danemark";
if pn='06'	 then pnaid="espagne";
if pn='07'	 then pnaid="finlande";
if pn='08'	 then pnaid="grece";
if pn='09'	 then pnaid="hongrie";
if pn='10'	 then pnaid="irlande";
if pn='11'	 then pnaid="italie";
if pn='12'	 then pnaid="luxembourg";
if pn='13'	 then pnaid="norvege";
if pn='14'	 then pnaid="pays-bas";
if pn='15'	 then pnaid="pologne";
if pn='16'	 then pnaid="portugal";
if pn='17'	 then pnaid="roumanie";
if pn='18'	 then pnaid="uk";
if pn='19'	 then pnaid="suede";
if pn='20'	 then pnaid="suisse";
if pn='21'	 then pnaid="tchecoslovaquie";
if pn='22'	 then pnaid="yougoslavie";
if pn='33'	 then pnaid="cameroun";
if pn='34'	 then pnaid="centrafrique";
if pn='35'	 then pnaid="congo";
if pn='36'	 then pnaid="cote divoire";
if pn='37'	 then pnaid="dahomey";
if pn='39'	 then pnaid="gabon";
if pn='40'	 then pnaid="guinee";
if pn='42'	 then pnaid="haute-volta";
if pn='43'	 then pnaid="madagascar";
if pn='44'	 then pnaid="mali";
if pn='46'	 then pnaid="mauritanie";
if pn='47'	 then pnaid="niger";
if pn='48'	 then pnaid="egypte";
if pn='49'	 then pnaid="senegal";
if pn='50'	 then pnaid="tchad";
if pn='51'	 then pnaid="togo";
if pn='71'	 then pnaid="cambodge";
if pn='72'	 then pnaid="chine";
if pn='73'	 then pnaid="inde";
if pn='74'	 then pnaid="iran";
if pn='75'	 then pnaid="israel";
if pn='76'	 then pnaid="japon";
if pn='77'	 then pnaid="laos";
if pn='78'	 then pnaid="liban";
if pn='79'	 then pnaid="syrie";
if pn='80'	 then pnaid="turquie";
if pn='81'	 then pnaid="vietnam";
if pn='85'	 then pnaid="urss";
/********************************/
if pn='31' or pn="30" then pnaid="Algeri";
if pn='45' then pnaid="Marocc";
if pn='52' then pnaid="Tunisi";
/*******************************/
if immig=0 then pnai="Natif";
if immig=0 then pnaid="Natif";
if missing(pnaid) then pnaid=pnai;
run;


data table;
set table;
/* Marseille */
if cgeo="13201" then cgeo="13055";
if cgeo="13202" then cgeo="13055";
if cgeo="13203" then cgeo="13055";
if cgeo="13204" then cgeo="13055";
if cgeo="13205" then cgeo="13055";
if cgeo="13206" then cgeo="13055";
if cgeo="13207" then cgeo="13055";
if cgeo="13208" then cgeo="13055";
if cgeo="13209" then cgeo="13055";
if cgeo="13210" then cgeo="13055";
if cgeo="13211" then cgeo="13055";
if cgeo="13212" then cgeo="13055";
if cgeo="13213" then cgeo="13055";
if cgeo="13214" then cgeo="13055";
if cgeo="13215" then cgeo="13055";
if cgeo="13216" then cgeo="13055";
/* Lyon */
if cgeo="69381" then cgeo="69123";
if cgeo="69382" then cgeo="69123";
if cgeo="69383" then cgeo="69123";
if cgeo="69384" then cgeo="69123";
if cgeo="69385" then cgeo="69123";
if cgeo="69386" then cgeo="69123";
if cgeo="69387" then cgeo="69123";
if cgeo="69388" then cgeo="69123";
if cgeo="69389" then cgeo="69123";
/* Paris */
if d="75" then cgeo="75056";
/* Corse */
if d="20" then delete;
/*******************************/	
if cgeo="02300" then cgeo="02738";
if cgeo="02630" then cgeo="02739";
if cgeo="42250" then cgeo="42279";
if cgeo="42309" then cgeo="42218";
if cgeo="44042" then cgeo="44131";
if cgeo="50383" then cgeo="50129";
if cgeo="57450" then cgeo="57306";
if cgeo="57458" then cgeo="57240";
if cgeo="57758" then cgeo="57757";
if cgeo="59020" then cgeo="59009";
if cgeo="59235" then cgeo="59009";
if cgeo="59298" then cgeo="59350";
if cgeo="59355" then cgeo="59350";
if cgeo="59373" then cgeo="59183";
if cgeo="59460" then cgeo="59183";
if cgeo="59510" then cgeo="59183";
if cgeo="62482" then cgeo="62178";
if cgeo="62575" then cgeo="62473";
if cgeo="69274" then cgeo="69286";
if cgeo="71467" then cgeo="71014";
if cgeo="76466" then cgeo="76217";
if cgeo="79248" then cgeo="79191";
/********************************/
if cgeo="01018"	then cgeo="01033";
if cgeo="29092"	then cgeo="29241";
if cgeo="33041"	then cgeo="33042";
if cgeo="42144"	then cgeo="42147";
if cgeo="49016"	then cgeo="49328";
if cgeo="49287"	then cgeo="49328";
if cgeo="49293"	then cgeo="49328";
if cgeo="51401"	then cgeo="51454";
if cgeo="57766"	then cgeo="57306";
if cgeo="62098"	then cgeo="62427";
if cgeo="62110"	then cgeo="62473";
if cgeo="71049"	then cgeo="71294";
if cgeo="71114"	then cgeo="71263";
if cgeo="79017"	then cgeo="79195";
if cgeo="79237"	then cgeo="79079";
if cgeo="79266"	then cgeo="79191";
if cgeo="79344"	then cgeo="79061";
if cgeo="85228"	then cgeo="85288";
run;

/* Merge with employment zones codes */
%macro ze;
/****************************/
proc sort data=table;
by cgeo;
run;
proc sort data=result.ze_cgeo_reg;
by cgeo;
run;

data table;
merge table (in = x) result.ze_cgeo_reg (keep = cgeo ze dep reg);
by cgeo;
if x;
run;
%mend ze;

/* Urban areas 1999 */
%macro corrUU99(year);
/******************/
/* Macro corrUU99 */
/******************/
/* correspondence UU99 */

/***************************************/
/* Ajuster pour PARIS LYON & MARSEILLE */
title "&year";

/* Premi�re �tape: remplace le CC du RP avec avec le CC2012 */
/* merge avec cg12 */
proc sort data=table;
by depcom;
run;
data cg12;
set ccom.cg12;
run;
proc sort data=cg12 nodupkeys;
by depcom;
run;

/* correction Paris, Lyon, Marseille */
data table;
merge table (in = x) cg12;
by depcom;
if x;
/* correction car cc pour quartiers dans les RP68 & 75 pour Paris
et non cc commune */
if missing(cg12) & d="75" then cg12="75056";
run;

data table;
set table;
mcg12=missing(cg12);
run;
title "&year part de mauvaise fusion avec cc12 ";
proc freq data=table;
table mcg12;
run;
/* Deuxi�me �tape: introduit 
/* merge avec UU99 */
proc sort data=table;
by cg12;
run;
data table;
merge table (in = x) ccom.uu99;
by cg12;
if x;
run;
data table;
set table;
muu99=missing(uu99);
ville=uu99;
run;

proc freq data=table;
table muu99;
run;
title "&year part de mauvaise fusion avec uu90";
data problem;
set table;
if not(missing(uu99)) then delete;
run;
title "&year communes non fusionn�es avec uu90";
proc freq data=problem;
table depcom;
run;
%mend corrUU99;

%macro immshare(reg,year);
/*******************************************************/
/* Compute immigrant share per region for each country */
/* ALL origins All education levels */
/*******************************************************/
/* Immigrant and natives among emloyees only */
data tabEMP;
set table;
if emploi=0 then delete;
run;
proc sort data=tabEMP;
by &reg;
run;
proc freq data=tabEMP noprint;
weight sond;
by &reg;
table immig / out=emp;
run;

data natEmp;
set emp;
if immig=1 then delete;
rename count=natEmp;
label count=natEmp;
drop immig percent;
run;

data imEmp;
set emp;
if immig=0 then delete;
rename count=imEmp;
label count=imEmp;
drop immig percent;
run;

data table.&reg.popEMP&year;
merge natEmp imEmp ;
by &reg;
if missing(imEmp) then imEmp=0;
zeEmp=natEmp+imEmp;
year="&year";
run;

proc sort data=table;
by pnaid;
run;

/* population EMPLOYEES of the zone */
proc freq data=table noprint;
weight sond;
table &reg / out=pop;
run;
data pop;
set pop;
rename count=&reg.pop;
keep &reg count;
run;
/* distribution of immigrant groups by PNAID */
proc freq data=table noprint;
by pnaid;
weight sond;
table &reg / out=&reg.count_&year;
run;
proc sort data=pop;
by &reg;
run;
data &reg.count_&year;
set &reg.count_&year (keep = pnaid &reg percent count);
rename percent=imshare;
rename count=niml;
run;
proc sort data=&reg.count_&year;
by &reg;
run;
data &reg.count_&year;
merge &reg.count_&year pop;
by &reg;
run;
data table.&reg.count_&year;
set &reg.count_&year;
year="&year";
run;
%mend immshare;

%macro instru(ze,year);
data table;
set table;
if age<16 then delete;
if age>60 then delete;
if milit=1 then delete;
if etud=1 then delete;
/************************************************/
/* define low skill immigrants education <= Bac */
lsk=(educ="CEPP" | educ="BEPC" | educ="NBAC");
immlsk=immig*lsk;
run;
%immshare(&ze,&year);
proc sort data=table ; 
by &ze;
run;
/* Version 2: only NEW immigrants AT THE NATIONAL LEVEL */
data tableNI;
set table;
if nimmig=0 then delete;
run;
proc freq data=tableNI noprint;
weight sond;
table pnaid / out=pnaid_&year;
run;
data table.NIpnaid_&year;
set pnaid_&year;
year="&year";
run;
%mend;
%ze;
%corrUU99(1968);
%instru(ze,1968);
%instru(ville,1968);

/********/
/* 1975 */
/********/
Data table;
set 
tbrp.rp75 (keep = d c1 s ad ta nc pn dip sond cm1)
;
run;
Data table;
length cgeo $5;
set table;
/* create municipality code */
cgeo=cats(d,c1);
depcom=cgeo;
drop c1;
/* Corsica !!! */
if d="2A" or d="2B" then delete;
/* sexe */
if s=1 then homme=1;
else homme=0;
drop s;
/* age */
age=AD*1;
drop ad;
/* LF statut */
/* actifs et non �tudiant */
if TA=1 then emploi=1;
else emploi=0;
if TA=1 or TA=3 then actif=1;
else actif=0;
if TA=5 then etud=1;
else etud=0;
if TA=6 then milit=1;
else milit=0;
if TA=3 then chom=1;
else chom=0;
drop ta;

/* immigrant status */
if  not(NC=1) then immig=1;
else immig=0;
drop nc;
/* living abroad in 1968 */
if cm1=7 then abd=1;
else abd=0;
drop cm1;
/* new immigrants */
if immig=1 and abd=1 then nimmig=1;
else nimmig=0;

/* people born in France are not considered immigrant */
if pn='**' or pn='91' or pn='92' or pn='93' or pn='94' or pn='95' or pn='96' or pn='97' or pn='98' then immig=0; 
/* education */
/* 4 groupes: {CEPP,BEPC,etc...},{Niveau Bac:brevet pro,etc},{Bac},{Bac+}*/
if DIP='10' or DIP='11' then educ='CEPP';
if DIP='20' or DIP='30' or DIP='31' then educ='BEPC';
if DIP='40' or DIP='41' or DIP='42' or DIP='43' or DIP='44' or DIP='45' then educ='NBAC';
if DIP='50' or DIP='51' or DIP='52' or DIP='53' or DIP='54' or DIP='55' then educ='BACP';
drop dip;
run;

data table;
set table;
/* Marseille */
if cgeo="13201" then cgeo="13055";
if cgeo="13202" then cgeo="13055";
if cgeo="13203" then cgeo="13055";
if cgeo="13204" then cgeo="13055";
if cgeo="13205" then cgeo="13055";
if cgeo="13206" then cgeo="13055";
if cgeo="13207" then cgeo="13055";
if cgeo="13208" then cgeo="13055";
if cgeo="13209" then cgeo="13055";
if cgeo="13210" then cgeo="13055";
if cgeo="13211" then cgeo="13055";
if cgeo="13212" then cgeo="13055";
if cgeo="13213" then cgeo="13055";
if cgeo="13214" then cgeo="13055";
if cgeo="13215" then cgeo="13055";
if cgeo="13216" then cgeo="13055";
/* Lyon */
if cgeo="69381" then cgeo="69123";
if cgeo="69382" then cgeo="69123";
if cgeo="69383" then cgeo="69123";
if cgeo="69384" then cgeo="69123";
if cgeo="69385" then cgeo="69123";
if cgeo="69386" then cgeo="69123";
if cgeo="69387" then cgeo="69123";
if cgeo="69388" then cgeo="69123";
if cgeo="69389" then cgeo="69123";
/* Paris */
if d="75" then cgeo="75056";
/* Corse */
if d="20" then delete;
/*****************************/
if cgeo="50383" then cgeo="50129";
if cgeo="59298" then cgeo="59350";
if cgeo="59355" then cgeo="59350";
if cgeo="76466" then cgeo="76217";
run;

data table;
set table;
/* origins */
/* use the country of birth */
if not(pn='**') then pn2=input(pn,best4.);
/* europe */
if pn2=>0 and pn2<=29 then pnai='Europe';
/* dom tom */
/*if pn2=>91 and pn2<=98 then pnai="Europe";*/
/* Africa */
if pn2=>31 and pn2<=59 then pnai="Africa";
/* Asia */
if pn2=>71 and pn2<=85 then pnai="Asia";
/* Other */
/* Amerique */
if pn2=>60 and pn2<=69 then pnai="Other";
/* Oceanie */
if pn2=>86 and pn2<=89 then pnai="Other";
/* */
if pn2=15 then pnai='Europe'; /*"Pologn";*/
if pn2=16 then pnai='Europe'; /*"Portug";*/
if pn2=06 then pnai='Europe'; /*"Spain";*/
if pn2=11 then pnai='Europe'; /*"Italy";*/
if pn2=31 then pnai="Maghreb";
if pn2=45 then pnai="Maghreb";
if pn2=52 then pnai="Maghreb";
if pn2=71 then pnai="Asia"; /* "Cambod";*/
if pn2=80 then pnai="Asia"; /* "Turkey";*/
if pn2=78 then pnai="Asia"; /* "Liban";*/
if pn2=81 then pnai="Asia"; /* "Vietna";*/
pnaic=pnai;
length pnaid $ 15;
if pn='01'	 then pnaid="allemagne";
if pn='02'	 then pnaid="autriche";
if pn='03'	 then pnaid="belgique";
if pn='04'	 then pnaid="bulgarie";
if pn='05'	 then pnaid="danemark";
if pn='06'	 then pnaid="espagne";
if pn='07'	 then pnaid="finlande";
if pn='08'	 then pnaid="grece";
if pn='09'	 then pnaid="hongrie";
if pn='10'	 then pnaid="irlande";
if pn='11'	 then pnaid="italie";
if pn='12'	 then pnaid="luxembourg";
if pn='13'	 then pnaid="norvege";
if pn='14'	 then pnaid="pays-bas";
if pn='15'	 then pnaid="pologne";
if pn='16'	 then pnaid="portugal";
if pn='17'	 then pnaid="roumanie";
if pn='18'	 then pnaid="uk";
if pn='19'	 then pnaid="suede";
if pn='20'	 then pnaid="suisse";
if pn='21'	 then pnaid="tchecoslovaquie";
if pn='22'	 then pnaid="yougoslavie";
if pn='33'	 then pnaid="cameroun";
if pn='34'	 then pnaid="centrafrique";
if pn='35'	 then pnaid="congo";
if pn='36'	 then pnaid="cote divoire";
if pn='37'	 then pnaid="dahomey";
if pn='39'	 then pnaid="gabon";
if pn='40'	 then pnaid="guinee";
if pn='42'	 then pnaid="haute-volta";
if pn='43'	 then pnaid="madagascar";
if pn='44'	 then pnaid="mali";
if pn='46'	 then pnaid="mauritanie";
if pn='47'	 then pnaid="niger";
if pn='48'	 then pnaid="egypte";
if pn='49'	 then pnaid="senegal";
if pn='50'	 then pnaid="tchad";
if pn='51'	 then pnaid="togo";
if pn='71'	 then pnaid="cambodge";
if pn='72'	 then pnaid="chine";
if pn='73'	 then pnaid="inde";
if pn='74'	 then pnaid="iran";
if pn='75'	 then pnaid="israel";
if pn='76'	 then pnaid="japon";
if pn='77'	 then pnaid="laos";
if pn='78'	 then pnaid="liban";
if pn='79'	 then pnaid="syrie";
if pn='80'	 then pnaid="turquie";
if pn='81'	 then pnaid="vietnam";
if pn='85'	 then pnaid="urss";
if pn='31' then pnaid="Algeri";
if pn='45' then pnaid="Marocc";
if pn='52' then pnaid="Tunisi";
if missing(pnaid) then pnaid=pnai;
/*******************************/
if immig=0 then pnai="Natif";
if immig=0 then pnaid="Natif";
if missing(pnaid) then pnaid=pnai;
run;

%ze;
%corrUU99(1975);
%instru(ze,1975);
%instru(ville,1975);

/********/
/* 1982 */
/********/
libname rp82 "S:\RP\RP 1982\Exploitation complementaire";
Data table;
set 
tbrp.rp82 (keep = d c s ad ta in pn dip sond cm2)
;
run;
Data table;
length cgeo $5;
set table;
/* create municipality code */
cgeo=cats(d,c);
depcom=cgeo;
drop c;
/* Corsica !!! */
if d="2A" or d="2B" then delete;
/* sexe */
if s=1 then homme=1;
else homme=0;
drop s;
/* age */
age=AD*1;
drop ad;
/* LF statut */
/* actifs et non �tudiant */
if TA=1 then emploi=1;
else emploi=0;
if TA=1 or TA=3 then actif=1;
else actif=0;
if TA=5 then etud=1;
else etud=0;
if TA=6 then milit=1;
else milit=0;
if TA=3 then chom=1;
else chom=0;
drop ta;
/* immigrant status */
if  not(IN=11) then immig=1;
else immig=0;
drop in;
/* people born in France are not considered immigrant */
if pn='**' or pn='91' or pn='92' or pn='93' or pn='94' or pn='95' or pn='96' or pn='97' or pn='98' then immig=0; 

/* living abroad in 1975 */
if cm2="8" then abd=1;
else abd=0;
drop cm2;

/* new immigrants */
if immig=1 and abd=1 then nimmig=1;
else nimmig=0;
/* education */
/* 4 groupes: {CEPP,BEPC,etc...},{Niveau Bac:brevet pro,etc},{Bac},{Bac+}*/
if DIP='01' or DIP='02' then educ='CEPP';
if DIP='10' or DIP='21' or DIP='22' then educ='BEPC';
if DIP='31' or DIP='32' or DIP='33' or DIP='34' then educ='NBAC';
if DIP='41' or DIP='42' or DIP='43' or DIP='44' or DIP='51' or DIP='52' then educ='BACP';
drop dip;
run;

data table;
set table;
/* Paris */
if d="75" then cgeo="75056";
/* Corse */
if d="20" then delete;
if cgeo="50383" then cgeo="50129";
if cgeo="59355" then cgeo="59350";
/*******/
if cgeo="02630" then cgeo="02738";
if cgeo="53024" then cgeo="53062";
if cgeo="62575" then cgeo="62473";
/******/
run;

data table;
set table;
/* origins */
/* use the country of birth */
/* origins */
/* use the country of birth */
if not(pn='**') then pn2=input(pn,best4.);
/* europe */
if pn2=>0 and pn2<=29 then pnai='Europe';
/* dom tom */
/*if pn2=>91 and pn2<=98 then pnai="Europe";*/
/* Africa */
if pn2=>31 and pn2<=59 then pnai="Africa";
/* Asia */
if pn2=>71 and pn2<=85 then pnai="Asia";
/* Other */
/* Amerique */
if pn2=>60 and pn2<=69 then pnai="Other";
/* Oceanie */
if pn2=>86 and pn2<=89 then pnai="Other";
/* */
if pn2=15 then pnai='Europe'; /* "Pologn";*/
if pn2=16 then pnai='Europe'; /* "Portug";*/
if pn2=06 then pnai='Europe'; /* "Spain";*/
if pn2=11 then pnai='Europe'; /* "Italy";*/
if pn2=31 then pnai="Maghreb";
if pn2=45 then pnai="Maghreb";
if pn2=52 then pnai="Maghreb";
if pn2=80 then pnai="Asia"; /* "Turkey";*/
if pn2=78 then pnai="Asia"; /* "Liban";*/
pnaic=pnai;
length pnaid $ 15;
if pn='01'	 then pnaid="allemagne";
if pn='02'	 then pnaid="autriche";
if pn='03'	 then pnaid="belgique";
if pn='04'	 then pnaid="bulgarie";
if pn='05'	 then pnaid="danemark";
if pn='06'	 then pnaid="espagne";
if pn='07'	 then pnaid="finlande";
if pn='08'	 then pnaid="grece";
if pn='09'	 then pnaid="hongrie";
if pn='10'	 then pnaid="irlande";
if pn='11'	 then pnaid="italie";
if pn='12'	 then pnaid="luxembourg";
if pn='13'	 then pnaid="norvege";
if pn='14'	 then pnaid="pays-bas";
if pn='15'	 then pnaid="pologne";
if pn='16'	 then pnaid="portugal";
if pn='17'	 then pnaid="roumanie";
if pn='18'	 then pnaid="uk";
if pn='19'	 then pnaid="suede";
if pn='20'	 then pnaid="suisse";
if pn='21'	 then pnaid="tchecoslovaquie";
if pn='22'	 then pnaid="yougoslavie";
if pn='33'	 then pnaid="cameroun";
if pn='34'	 then pnaid="centrafrique";
if pn='35'	 then pnaid="congo";
if pn='36'	 then pnaid="cote divoire";
if pn='37'	 then pnaid="dahomey";
if pn='39'	 then pnaid="gabon";
if pn='40'	 then pnaid="guinee";
if pn='42'	 then pnaid="haute-volta";
if pn='43'	 then pnaid="madagascar";
if pn='44'	 then pnaid="mali";
if pn='46'	 then pnaid="mauritanie";
if pn='47'	 then pnaid="niger";
if pn='48'	 then pnaid="egypte";
if pn='49'	 then pnaid="senegal";
if pn='50'	 then pnaid="tchad";
if pn='51'	 then pnaid="togo";
if pn='71'	 then pnaid="cambodge";
if pn='72'	 then pnaid="chine";
if pn='73'	 then pnaid="inde";
if pn='74'	 then pnaid="iran";
if pn='75'	 then pnaid="israel";
if pn='76'	 then pnaid="japon";
if pn='77'	 then pnaid="laos";
if pn='78'	 then pnaid="liban";
if pn='79'	 then pnaid="syrie";
if pn='80'	 then pnaid="turquie";
if pn='81'	 then pnaid="vietnam";
if pn='85'	 then pnaid="urss";
/*******************/
if pn='31' then pnaid="Algeri";
if pn='45' then pnaid="Marocc";
if pn='52' then pnaid="Tunisi";
if missing(pnaid) then pnaid=pnai;
/*******************************/
if immig=0 then pnai="Natif";
if immig=0 then pnaid="Natif";
if missing(pnaid) then pnaid=pnai;
run;

%ze;
%corrUU99(1982);
%instru(ze,1982);
%instru(ville,1982);

/********/
/* 1990 */
/********/
libname rp90 "S:\RP\RP 1990\Exploitation complementaire";
libname rp902 "S:\RP\RP 1990\Exploitation complementaire\region 11";

data table;
set 
tbrp.rp90 (keep = age d c sexe tact nat1 inai dipl1 sond pnai cmig)
;
/* sond */
newsond = input(sond,4.0); 
drop sond; 
rename newsond=sond; 
run;

data table;
set table;
length cgeo $5;
set table;
/* create municipality code */
cgeo=cats(d,c);
depcom=cgeo;
drop c;
/***********/
/* Corsica !!! */
if d="2A" or d="2B" then delete;
/* sexe */
if sexe=1 then homme=1;
else homme=0;
drop sexe;
/* age */
age=Age*1;
/* LF statut */
/* actifs et non �tudiant */
if TACT=11 then emploi=1;
else emploi=0;
if TACT=11 or TACT=12 then actif=1;
else actif=0;
if TACT=22 then etud=1;
else etud=0;
if TACT=13 then milit=1;
else milit=0;
if TACT=12 then chom=1;
else chom=0;
drop tact;
/* immigrant status */
if  not(NAT1=11) then immig=1;
else immig=0;
drop nat1;
/* not born in Fce */
if not(INAI='4') then immig=0;
drop inai;
/* living abroad in 1990 */
if cmig=7 then abd=1;
else abd=0;
drop cmig;
/* new immigrants */
if immig=1 and abd=1 then nimmig=1;
else nimmig=0;
/* education */
/* 4 groupes: {CEPP,BEPC,etc...},{Niveau Bac:brevet pro,etc},{Bac},{Bac+}*/
if DIPL1='2' or DIPL1='3' then educ='CEPP';
if DIPL1='4' or DIPL1='5' or DIPL1='6' then educ='BEPC';
if DIPL1='7' then educ='NBAC';
if DIPL1='8' or DIPL1='9' then educ='BACP';
run;

data table;
set table;
/* Marseille */
if cgeo="13201" then cgeo="13055";
if cgeo="13202" then cgeo="13055";
if cgeo="13203" then cgeo="13055";
if cgeo="13204" then cgeo="13055";
if cgeo="13205" then cgeo="13055";
if cgeo="13206" then cgeo="13055";
if cgeo="13207" then cgeo="13055";
if cgeo="13208" then cgeo="13055";
if cgeo="13209" then cgeo="13055";
if cgeo="13210" then cgeo="13055";
if cgeo="13211" then cgeo="13055";
if cgeo="13212" then cgeo="13055";
if cgeo="13213" then cgeo="13055";
if cgeo="13214" then cgeo="13055";
if cgeo="13215" then cgeo="13055";
if cgeo="13216" then cgeo="13055";
/* Lyon */
if cgeo="69381" then cgeo="69123";
if cgeo="69382" then cgeo="69123";
if cgeo="69383" then cgeo="69123";
if cgeo="69384" then cgeo="69123";
if cgeo="69385" then cgeo="69123";
if cgeo="69386" then cgeo="69123";
if cgeo="69387" then cgeo="69123";
if cgeo="69388" then cgeo="69123";
if cgeo="69389" then cgeo="69123";
/* Paris */
if d="75" then cgeo="75056";
/* Corse */
if d="20" then delete;
if cgeo="50383" then cgeo="50129";
if cgeo="59355" then cgeo="59350";
/***/
if cgeo="62575" then cgeo="62473";
if cgeo="79017" then cgeo="79195";
run;

data table;
set table;
/* country of origins */
/* origins */
/* use the country of birth */
/* europe */
if 
pnai='007' or 
pnai='008' or 
pnai='020' or 
pnai='040' or 
pnai='056' or 
pnai='100' or 
pnai='196' or 
pnai='200' or 
pnai='208' or 
pnai='234' or 
pnai='238' or 
pnai='246' or 
pnai='278' or 
pnai='279' or 
pnai='280' or 
pnai='292' or 
pnai='300' or 
pnai='304' or 
pnai='308' or 
pnai='336' or 
pnai='348' or 
pnai='352' or 
pnai='372' or 
pnai='438' or 
pnai='442' or 
pnai='470' or 
pnai='492' or 
pnai='528' or 
pnai='574' or 
pnai='578' or 
pnai='642' or 
pnai='654' or 
pnai='674' or 
pnai='744' or 
pnai='752' or 
pnai='756' or 
pnai='826' or 
pnai='890' 
then pnaic='Europe';
/* Asia */
if 
pnai='004' or
pnai='006' or
pnai='048' or
pnai='050' or
pnai='064' or
pnai='096' or
pnai='104' or
pnai='156' or
pnai='158' or
pnai='162' or
pnai='166' or
pnai='168' or
pnai='274' or
pnai='334' or
pnai='344' or
pnai='356' or
pnai='360' or
pnai='368' or
pnai='376' or
pnai='392' or
pnai='400' or
pnai='408' or
pnai='409' or
pnai='410' or
pnai='414' or
pnai='446' or
pnai='458' or
pnai='462' or
pnai='480' or
pnai='496' or
pnai='512' or
pnai='524' or
pnai='586' or
pnai='608' or
pnai='626' or
pnai='634' or
pnai='682' or
pnai='702' or
pnai='720' or
pnai='760' or
pnai='764' or
pnai='784' or
pnai='810' or
pnai='886' or
pnai='144'
then pnaic="Asia";
/* Africa */
if 
pnai='002' or 
pnai='072' or 
pnai='108' or 
pnai='120' or 
pnai='132' or 
pnai='140' or 
pnai='148' or 
pnai='174' or 
pnai='178' or 
pnai='204' or 
pnai='226' or 
pnai='230' or 
pnai='262' or 
pnai='266' or 
pnai='270' or 
pnai='272' or 
pnai='288' or 
pnai='324' or 
pnai='384' or 
pnai='404' or 
pnai='426' or 
pnai='430' or 
pnai='434' or 
pnai='450' or 
pnai='454' or 
pnai='466' or 
pnai='478' or 
pnai='508' or 
pnai='516' or 
pnai='562' or 
pnai='566' or 
pnai='624' or 
pnai='646' or 
pnai='678' or 
pnai='686' or 
pnai='690' or 
pnai='694' or 
pnai='706' or 
pnai='710' or 
pnai='716' or 
pnai='732' or 
pnai='736' or 
pnai='748' or 
pnai='768' or 
pnai='800' or 
pnai='818' or 
pnai='834' or 
pnai='854' or 
pnai='894' or
pnai='024' or
pnai='180'
then pnaic="Africa";
/* Other */
/* Amerique */
if 
pnai='016' or
pnai='019' or
pnai='032' or
pnai='028' or
pnai='040' or
pnai='044' or
pnai='052' or
pnai='060' or
pnai='068' or
pnai='074' or
pnai='076' or
pnai='084' or
pnai='092' or
pnai='124' or
pnai='136' or
pnai='152' or
pnai='170' or
pnai='188' or
pnai='192' or
pnai='214' or
pnai='218' or
pnai='222' or
pnai='320' or
pnai='328' or
pnai='340' or
pnai='388' or
pnai='484' or
pnai='500' or
pnai='532' or
pnai='533' or
pnai='558' or
pnai='590' or
pnai='600' or
pnai='604' or
pnai='630' or
pnai='659' or
pnai='660' or
pnai='662' or
pnai='670' or
pnai='740' or
pnai='780' or
pnai='796' or
pnai='840' or
pnai='850' or
pnai='858' or
pnai='862'
then pnaic="Other";
/* Oceanie */
if pnai='009' or 
pnai='036' or 
pnai='090' or 
pnai='184' or 
pnai='212' or 
pnai='242' or 
pnai='260' or 
pnai='296' or 
pnai='316' or 
pnai='520' or 
pnai='548' or 
pnai='554' or 
pnai='570' or 
pnai='580' or 
pnai='584' or 
pnai='585' or 
pnai='598' or 
pnai='612' or 
pnai='772' or 
pnai='776' or 
pnai='332' or
pnai='882'
then pnaic="Other";
if pnai='616' then pnaic='Europe'; /* "Pologne"; */
if pnai='620' then pnaic='Europe'; /* "Portugal"; */
if pnai='724' then pnaic='Europe'; /* "Spain"; */
if pnai='380' then pnaic='Europe'; /* "Italy"; */
if pnai='012' then pnaic="Maghreb";
if pnai='504'	then pnaic="Maghreb";
if pnai='788'	then pnaic="Maghreb";
if pnai='792'	then pnaic="Asia"; /* "Turkey";   */
if pnai='422'	then pnaic="Asia"; /* "Liban";    */
length pnaid $ 15;
if pnai='278' or pnai='279' or pnai='280'	then 	pnaid='allemagne'	;
if pnai='040'	then 	pnaid='autriche'	;
if pnai='056'	then 	pnaid='belgique'	;
if pnai='100'	then 	pnaid='bulgarie'	;
if pnai='208'	then 	pnaid='danemark'	;
if pnai='724'	then 	pnaid='espagne'	;
if pnai='246'	then 	pnaid='finlande'	;
if pnai='300'	then 	pnaid='grece'	;
if pnai='348'	then 	pnaid='hongrie'	;
if pnai='372'	then 	pnaid='irlande'	;
if pnai='380'	then 	pnaid='italie'	;
if pnai='442'	then 	pnaid='luxembourg'	;
if pnai='578'	then 	pnaid='norvege'	;
if pnai='528'	then 	pnaid='pays-bas'	;
if pnai='616'	then 	pnaid='pologne'	;
if pnai='620'	then 	pnaid='portugal'	;
if pnai='642'	then 	pnaid='roumanie'	;
if pnai='826'	then 	pnaid='uk'	;
if pnai='752'	then 	pnaid='suede'	;
if pnai='756'	then 	pnaid='suisse'	;
if pnai='200'	then 	pnaid='tchecoslovaquie'	;
if pnai='890'	then 	pnaid='yougoslavie'	;
if pnai='120'	then 	pnaid='cameroun'	;
if pnai='140'	then 	pnaid='centrafrique'	;
if pnai='178'	then 	pnaid='congo'	;
if pnai='384'	then 	pnaid='cote divoire'	;
if pnai='204'	then 	pnaid='dahomey'	;
if pnai='266'	then 	pnaid='gabon'	;
if pnai='324'	then 	pnaid='guinee'	;
if pnai='854'	then 	pnaid='haute-volta'	;
if pnai='450'	then 	pnaid='madagascar'	;
if pnai='466'	then 	pnaid='mali'	;
if pnai='478'	then 	pnaid='mauritanie'	;
if pnai='562'	then 	pnaid='niger'	;
if pnai='818'	then 	pnaid='egypte'	;
if pnai='686'	then 	pnaid='senegal'	;
if pnai='148'	then 	pnaid='tchad'	;
if pnai='768'	then 	pnaid='togo'	;
if pnai='116'	then 	pnaid='cambodge'	;
if pnai='156' or pnai='158'	then 	pnaid='chine'	;
if pnai='356'	then 	pnaid='inde'	;
if pnai='364'	then 	pnaid='iran'	;
if pnai='376'	then 	pnaid='israel'	;
if pnai='392'	then 	pnaid='japon'	;
if pnai='418'	then 	pnaid='laos'	;
if pnai='422'	then 	pnaid='liban'	;
if pnai='760'	then 	pnaid='syrie'	;
if pnai='792'	then 	pnaid='turquie'	;
if pnai='704'	then 	pnaid='vietnam'	;
if pnai='810'	then 	pnaid='urss'	;
if pnai='012' then pnaid="Algeri";
if pnai='504' then pnaid="Marocc";
if pnai='788' then pnaid="Tunisi";
if missing(pnaid) then pnaid=pnaic;
/*******************************/
if immig=0 then pnai="Natif";
if immig=0 then pnaid="Natif";
if missing(pnaid) then pnaid=pnai;
run;

%ze;
%corrUU99(1990);
%instru(ze,1990);
%instru(ville,1990);

/********/
/* 1999 */
/********/
libname rp99 'S:\RP\RP 1999\Exploitation compl�mentaire\Version 1';

/* select urban ares with more than 50 000 inhabitants
number of urban area: 114 in 1999 */
data table;
set 
tbrp.rp99
( keep = sexe catl d c age dipl inat1 inai2 tact sond pnai pnai3 iran
)
;

/**********************/
/* observation = humain being
if sex is missing then delete */
if missing(sexe) then delete;
/**************************************/
/* only keep 'residences principales '*/
if not(catl='1') then delete;

run;
/*************************/
/* creation des variables*/
/*************************/
data table;
length cgeo $5;
set table;
drop catl;
cgeo=cats(d,c);
depcom=cgeo;
/* age */
agenew=input(age, 8.0);
drop age;
rename agenew=age;
/* education*/
if dipl='0' or dipl='1' then educ='CEPP';
if dipl='2' or dipl='3' or dipl='4' then educ='BEPC';
if dipl='5' or dipl='6' then educ='NBAC';
if dipl='7' then educ='BAC1';
if dipl='8' then educ='BAC2';
/*immigres*/
if not(inat1=11)then immig=1;
else immig=0;
/* not born in france*/
if not(inai2='3') then immig=0;
drop inat1 inai2;
/* living abroad in 1990 */
if iran=7 or iran=8 then abd=1;
else abd=0;
drop iran;
/* new immigrants */
if immig=1 and abd=1 then nimmig=1;
else nimmig=0;
/* LF Statut */
emploi=(TACT=11);
if TACT=11 or TACT=12 then actif=1;
else actif=0;
if TACT=22 then etud=1;
else etud=0;
milit=(TACT=13);
chom=(TACT=12);
run;

data table;
set table;
/* Marseille */
if cgeo="13201" then cgeo="13055";
if cgeo="13202" then cgeo="13055";
if cgeo="13203" then cgeo="13055";
if cgeo="13204" then cgeo="13055";
if cgeo="13205" then cgeo="13055";
if cgeo="13206" then cgeo="13055";
if cgeo="13207" then cgeo="13055";
if cgeo="13208" then cgeo="13055";
if cgeo="13209" then cgeo="13055";
if cgeo="13210" then cgeo="13055";
if cgeo="13211" then cgeo="13055";
if cgeo="13212" then cgeo="13055";
if cgeo="13213" then cgeo="13055";
if cgeo="13214" then cgeo="13055";
if cgeo="13215" then cgeo="13055";
if cgeo="13216" then cgeo="13055";
/* Lyon */
if cgeo="69381" then cgeo="69123";
if cgeo="69382" then cgeo="69123";
if cgeo="69383" then cgeo="69123";
if cgeo="69384" then cgeo="69123";
if cgeo="69385" then cgeo="69123";
if cgeo="69386" then cgeo="69123";
if cgeo="69387" then cgeo="69123";
if cgeo="69388" then cgeo="69123";
if cgeo="69389" then cgeo="69123";
/* Paris */
if d="75" then cgeo="75056";
/* Corse */
if d="20" then delete;
if cgeo="50383" then cgeo="50129";
if cgeo="59355" then cgeo="59350";
/***/
if cgeo="62575" then cgeo="62473";
if cgeo="79017" then cgeo="79195";
run;

data table;
set table;
/****************************/
/* origins */
/* use the country of birth */
/* europe */
length pnaic $ 15;
pn2=pnai3*1;
if pn2=>11 and pn2<=15 then pnaic='Europe';
maghreb=(( pn2>=21 and pn2<=23))*immig;
/* Africa */
if pn2=24 & maghreb=0 then pnaic="Africa";
/* Asia */
if  pn2>=31 and pn2<=35 then pnaic="Asia";
/* Maghreb */
if pn2>=21 and pn2<=23 then pnaic="Maghreb";
if immig=0 then pnaic="Natif";
if  missing(pnaic) then pnaic="Other";
length pnaid $ 15;
if pnai='276' then pnaid="allemagne";
if pnai='040' then pnaid="autriche";
if pnai='056'	 then pnaid="belgique";
if pnai='100'	 then pnaid="bulgarie";
if pnai='208'	 then pnaid="danemark";
if pnai='724'	 then pnaid="espagne";
if pnai='246'	 then pnaid="finlande";
if pnai='300'	 then pnaid="grece";
if pnai='348'	 then pnaid="hongrie";
if pnai='372' then pnaid="irlande";
if pnai='380'	 then pnaid="italie";
if pnai='442'	 then pnaid="luxembourg";
if pnai='578'	 then pnaid="norvege";
if pnai='528'	 then pnaid="pays-bas";
if pnai='616'	 then pnaid="pologne";
if pnai='620'	 then pnaid="portugal";
if pnai='642'	 then pnaid="roumanie";
if pnai='826'	 then pnaid="uk";
if pnai='752'	 then pnaid="suede";
if pnai='756'	 then pnaid="suisse";
if pnai='200' or pnai="203"	 then pnaid="tchecoslovaquie";
if pnai='890' or pnai='891'	then pnaid="yougoslavie";
if pnai='120'	 then pnaid="cameroun";
if pnai='140'	 then pnaid="centrafrique";
if pnai='178'	 then pnaid="congo";
if pnai='384'	 then pnaid="cote divoire";
if pnai='204'	 then pnaid="dahomey";
if pnai='266'	 then pnaid="gabon";
if pnai='324'	 then pnaid="guinee";
if pnai='854'	 then pnaid="haute-volta";
if pnai='450'	 then pnaid="madagascar";
if pnai='466'	 then pnaid="mali";
if pnai='478'	 then pnaid="mauritanie";
if pnai='562'	 then pnaid="niger";
if pnai='818'	 then pnaid="egypte";
if pnai='686'	 then pnaid="senegal";
if pnai='148'	 then pnaid="tchad";
if pnai='768'	 then pnaid="togo";
if pnai='116'	 then pnaid="cambodge";
if pnai='156'	 then pnaid="chine";
if pnai='356'	 then pnaid="inde";
if pnai='364'	 then pnaid="iran";
if pnai='376'	 then pnaid="israel";
if pnai='392'	 then pnaid="japon";
if pnai='418'	 then pnaid="laos";
if pnai='422'	 then pnaid="liban";
if pnai='760'	 then pnaid="syrie";
if pnai='792'	 then pnaid="turquie";
if pnai='704'	 then pnaid="vietnam";
if pnai='643'	 then pnaid="urss";
/********************************/
if pnai='012' then pnaid="Algeri";
if pnai='504' then pnaid="Marocc";
if pnai='788' then pnaid="Tunisi";
/*******************************/
if immig=0 then pnaic="Natif";
if immig=0 then pnaid="Natif";
if missing(pnaid) then pnaid=pnaic;
run;

%ze;
%corrUU99(1999);
%instru(ze,1999);
%instru(ville,1999);

/***********/
/* RP 2007 */
/***********/
libname indiv "S:\RP\RP 2007\Expcomp\din";
libname men "S:\RP\RP 2007\Expcomp\dme";
libname fam "S:\RP\RP 2007\Expcomp\dfa";
/*proc contents data=indiv.r73;
run;
proc contents data=men.r73;
run;
proc contents data=fam.r73;
run;*/
/*******************/
/* table individus */
/*******************/
data table ;
set tbrp.rp07
(keep = 
/* identifiants individuels */
 sexe immi /*inat pnai12*/
/* socio demo carac */
tact dipl agerev
/* location */
/*region_lr */ dept_lr commune_lr 
/* nationalit�s */
nat natc natnc natn
sond iran arrivr
)
;
run;

data table;
set table;
length cgeo $5;
/* deux d�finitions d'immigr�s */
immig=(immi="1");
drop immi;
if natc="0" then immig=0;
/* new immigrants arriv� apr�s 1999*/
if iran='8' or iran='9' or (arrivr='9') then abd=1;
else abd=0;
nimmig=((abd=1) & immig=1);
/********************/
cgeo=commune_lr;
depcom=cgeo;
rename dept_lr= d;
/* education */
/* education*/
if dipl='01' or dipl='02' or dipl='03' or dipl='11' then educ='CEPP';
if dipl='12' or dipl='13' or dipl='14' then educ='BEPC';
if dipl='15' or dipl='16' then educ='NBAC';
if dipl='17' then educ='BAC1';
if dipl='18' then educ='BAC2';
/* chomage */
chom=(tact='12');
/* emploi */
emploi=(tact='11');
/* actif */
actif=(tact='11' | tact='12');
/* etudiant */
etud=(tact='22');
/* militaire */
milit=0;
/* age */
age=agerev*1;
/* nationalit� d'origine */
/* d�taill�e */
natf=nat;
if nat="998" then natf=natn;
/* condens�e */
natcf=natc;
if natc=1 then natcf=natnc;
run;
proc freq data=table;
weight sond;
table commune_lr;
run;
data table;
set table;
/* Marseille */
if cgeo="13201" then cgeo="13055";
if cgeo="13202" then cgeo="13055";
if cgeo="13203" then cgeo="13055";
if cgeo="13204" then cgeo="13055";
if cgeo="13205" then cgeo="13055";
if cgeo="13206" then cgeo="13055";
if cgeo="13207" then cgeo="13055";
if cgeo="13208" then cgeo="13055";
if cgeo="13209" then cgeo="13055";
if cgeo="13210" then cgeo="13055";
if cgeo="13211" then cgeo="13055";
if cgeo="13212" then cgeo="13055";
if cgeo="13213" then cgeo="13055";
if cgeo="13214" then cgeo="13055";
if cgeo="13215" then cgeo="13055";
if cgeo="13216" then cgeo="13055";
/* Lyon */
if cgeo="69381" then cgeo="69123";
if cgeo="69382" then cgeo="69123";
if cgeo="69383" then cgeo="69123";
if cgeo="69384" then cgeo="69123";
if cgeo="69385" then cgeo="69123";
if cgeo="69386" then cgeo="69123";
if cgeo="69387" then cgeo="69123";
if cgeo="69388" then cgeo="69123";
if cgeo="69389" then cgeo="69123";

if cgeo="50383" then cgeo="50129";
if cgeo="59355" then cgeo="59350";
/***/
if cgeo="62575" then cgeo="62473";
if cgeo="79017" then cgeo="79195";
run;

data table;
set table;
/****************************/
/* origins */
/* use the country of birth */
/* europe */
if natcf="2" OR natcf="3" then pnai='Europe';
maghreb=(natf='012' OR natf='504' OR natf='788')*immig;
/* Africa */
if (natcf='4') & maghreb=0 then pnai="Africa";
/* Asia */
if (natcf="5") then pnai="Asia";
/* Other */
if natcf="6" OR natcf="7" then pnai="Other";
/* Maghreb */
if natf='012' OR natf='504' OR natf='788' then pnai="Maghreb";
pnaic=pnai;
length pnaid $ 15;
if natf='276' then pnaid="allemagne";
if natf='040'	 then pnaid="autriche";
if natf='056'	 then pnaid="belgique";
if natf='100'	 then pnaid="bulgarie";
if natf='208'	 then pnaid="danemark";
if natf='724'	 then pnaid="espagne";
if natf='246'	 then pnaid="finlande";
if natf='300'	 then pnaid="grece";
if natf='348'	 then pnaid="hongrie";
if natf='372' then pnaid="irlande";
if natf='380'	 then pnaid="italie";
if natf='442'	 then pnaid="luxembourg";
if natf='578'	 then pnaid="norvege";
if natf='528'	 then pnaid="pays-bas";
if natf='616'	 then pnaid="pologne";
if natf='620'	 then pnaid="portugal";
if natf='642'	 then pnaid="roumanie";
if natf='826'	 then pnaid="uk";
if natf='752'	 then pnaid="suede";
if natf='756'	 then pnaid="suisse";
if natf='200'	 then pnaid="tchecoslovaquie";
if natf='890' or natf='891'	then pnaid="yougoslavie";
if natf='120'	 then pnaid="cameroun";
if natf='140'	 then pnaid="centrafrique";
if natf='178'	 then pnaid="congo";
if natf='384'	 then pnaid="cote divoire";
if natf='204'	 then pnaid="dahomey";
if natf='266'	 then pnaid="gabon";
if natf='324'	 then pnaid="guinee";
if natf='854'	 then pnaid="haute-volta";
if natf='450'	 then pnaid="madagascar";
if natf='466'	 then pnaid="mali";
if natf='478'	 then pnaid="mauritanie";
if natf='562'	 then pnaid="niger";
if natf='818'	 then pnaid="egypte";
if natf='686'	 then pnaid="senegal";
if natf='148'	 then pnaid="tchad";
if natf='768'	 then pnaid="togo";
if natf='116'	 then pnaid="cambodge";
if natf='156'	 then pnaid="chine";
if natf='356'	 then pnaid="inde";
if natf='364'	 then pnaid="iran";
if natf='376'	 then pnaid="israel";
if natf='392'	 then pnaid="japon";
if natf='418'	 then pnaid="laos";
if natf='422'	 then pnaid="liban";
if natf='760'	 then pnaid="syrie";
if natf='792'	 then pnaid="turquie";
if natf='704'	 then pnaid="vietnam";
if natf='643'	 then pnaid="urss";
/********************************/
if natf='012' then pnaid="Algeri";
if natf='504' then pnaid="Marocc";
if natf='788' then pnaid="Tunisi";
/*******************************/
if immig=0 then pnai="Natif";
if immig=0 then pnaid="Natif";
if missing(pnaid) then pnaid=pnai;
run;
proc freq data=table;
table pnai pnaid immig natcf;
run;

%ze;
%corrUU99(2007);
%instru(ze,2007);
%instru(ville,2007);

/********/
/* 2012 */
/********/
data table;
set tbrp.rp12;
depcom= commune_resid ;
d=substr(commune_resid,1,2);
cgeo=depcom;
run;
data table;
set table;
/* Marseille */
if cgeo="13201" then cgeo="13055";
if cgeo="13202" then cgeo="13055";
if cgeo="13203" then cgeo="13055";
if cgeo="13204" then cgeo="13055";
if cgeo="13205" then cgeo="13055";
if cgeo="13206" then cgeo="13055";
if cgeo="13207" then cgeo="13055";
if cgeo="13208" then cgeo="13055";
if cgeo="13209" then cgeo="13055";
if cgeo="13210" then cgeo="13055";
if cgeo="13211" then cgeo="13055";
if cgeo="13212" then cgeo="13055";
if cgeo="13213" then cgeo="13055";
if cgeo="13214" then cgeo="13055";
if cgeo="13215" then cgeo="13055";
if cgeo="13216" then cgeo="13055";
/* Lyon */
if cgeo="69381" then cgeo="69123";
if cgeo="69382" then cgeo="69123";
if cgeo="69383" then cgeo="69123";
if cgeo="69384" then cgeo="69123";
if cgeo="69385" then cgeo="69123";
if cgeo="69386" then cgeo="69123";
if cgeo="69387" then cgeo="69123";
if cgeo="69388" then cgeo="69123";
if cgeo="69389" then cgeo="69123";

if cgeo="50383" then cgeo="50129";
if cgeo="59355" then cgeo="59350";
/***/
if cgeo="62575" then cgeo="62473";
if cgeo="79017" then cgeo="79195";
run;
Data table;
set table;

length pnaic $7;
length pnaid $15;

/* observation = humain being
if sex is missing then delete */
if missing(sexe) then delete;

/* sexe */
homme=(sexe='1');

/* age */
age = input(agerev,3.0); 

/* couple */
if moco='21' or moco='22' then coup=1;
else coup=0;
/* couple with children */
if moco='22' then coupch=1;
else coupch=0;
/* famille monoparentale */
monop=(moco='23');
/* Head of Household */
hhd=(lprm='1');

/* immigrant status */
immig=(immi="1");
drop immi;
if natc="0" then immig=0;

/* new immigrants arriv� apr�s 1999*/
if iran='8' or iran='9' or (arrivr='9') then abd=1;
else abd=0;
nimmig=((abd=1) & immig=1);

/* europe & neurope */
/* nationalit� d'origine */
/* d�taill�e */
natf=nat;
if nat="998" then natf=natn;
/* condens�e */
natcf=natc;
if natc=1 then natcf=natnc;

europe=(natcf="2" OR natcf="3")*immig;
neurope=(1-europe)*immig;

/* r�gions harmonis�es PNAIC */
if (natcf="2" OR natcf="3") & immig=1 then pnaic="Europe";
if (natcf='4') & immig=1 then pnaic="Africa";
if (natf='012' OR natf='504' OR natf='788') then pnaic="Maghreb";
if (natf="116" or natf="418" or natf="704" or natf="156" or
natf="408" or natf="410" or natf="356" or natf="360" or
natf="392" or natf="586" or natf="608" or natf="144" or
natf="764") & immig=1 then pnaic="Asia";
if (natf="368" or natf="364" or natf="376" or natf="760" or
natf="422" or natf="792" or natf="860" or natf="762" or
natf="795" or natf="051" or natf="031" or natf="268" or
natf="398" or natf="417") & immig=1  then pnaic="Asia";
if immig=0 then pnaic="Natif";
if immig=1 & missing(pnaic) then pnaic="Other";

length pnaid $ 15;
/* pays harmonis�s PNAID */
if natf='276' then pnaid="allemagne";
if natf='040'	 then pnaid="autriche";
if natf='056'	 then pnaid="belgique";
if natf='100'	 then pnaid="bulgarie";
if natf='208'	 then pnaid="danemark";
if natf='724'	 then pnaid="espagne";
if natf='246'	 then pnaid="finlande";
if natf='300'	 then pnaid="grece";
if natf='348'	 then pnaid="hongrie";
if natf='372' then pnaid="irlande";
if natf='380'	 then pnaid="italie";
if natf='442'	 then pnaid="luxembourg";
if natf='578'	 then pnaid="norvege";
if natf='528'	 then pnaid="pays-bas";
if natf='616'	 then pnaid="pologne";
if natf='620'	 then pnaid="portugal";
if natf='642'	 then pnaid="roumanie";
if natf='826'	 then pnaid="uk";
if natf='752'	 then pnaid="suede";
if natf='756'	 then pnaid="suisse";
if natf='200'  | natf='703' | natf='203'	then 	pnaid='tchecoslovaquie'	;
if natf='890' or natf='891'	then pnaid="yougoslavie";
if natf='120'	 then pnaid="cameroun";
if natf='140'	 then pnaid="centrafrique";
if natf='178'	 then pnaid="congo";
if natf='384'	 then pnaid="cote divoire";
if natf='204'	 then pnaid="dahomey";
if natf='266'	 then pnaid="gabon";
if natf='324'	 then pnaid="guinee";
if natf='854'	 then pnaid="haute-volta";
if natf='450'	 then pnaid="madagascar";
if natf='466'	 then pnaid="mali";
if natf='478'	 then pnaid="mauritanie";
if natf='562'	 then pnaid="niger";
if natf='818'	 then pnaid="egypte";
/*********/
if natf='686'	 then pnaid="senegal";
if natf='148'	 then pnaid="tchad";
if natf='768'	 then pnaid="togo";
if natf='116'	 then pnaid="cambodge";
if natf='156' or natf='158'	then 	pnaid='chine'	;
if natf='356'	 then pnaid="inde";
if natf='364'	 then pnaid="iran";
if natf='376'	 then pnaid="israel";
if natf='392'	 then pnaid="japon";
if natf='418'	 then pnaid="laos";
if natf='422'	 then pnaid="liban";
if natf='760'	 then pnaid="syrie";
if natf='792'	 then pnaid="turquie";
if natf='704'	 then pnaid="vietnam";
if natf='643'	 then pnaid="urss";
if natf='643' | natf='804' | natf="398"	then 	pnaid='urss'	;
if natf='012' then pnaid="Algeri";
if natf='504' then pnaid="Marocc";
if natf='788' then pnaid="Tunisi";
if immig=0 then pnaid="Natif";
if immig=1 and missing(pnaid) then pnaid=pnaic;


/*******/
/*2007 */


/* fin 2007 */

/* Education : DIP */
if dipl='01' or dipl='02' or dipl='03' or dipl='11' then educ='CEPP';
if dipl='12' or dipl='13' or dipl='14' then educ='BEPC';
if dipl='15' or dipl='16' then educ='NBAC';
if dipl='17' then educ='BACP';
if dipl='18' then educ='BACP';

/* LF statut */
/* chomage */
chom=(tact='12');
/* actif */
actif=(tact='11' | tact='12');
etud=(tact='22' | tact='23');
/* emploi */
emploi=(tact='11');

run;

%ze;
%corrUU99(2012);
%instru(ze,2012);
%instru(ville,2012);

data table.Nipnaid;
set table.Nipnaid_1968 table.Nipnaid_1975 table.Nipnaid_1982 table.Nipnaid_1990 table.Nipnaid_1999 table.Nipnaid_2007 table.Nipnaid_2012 ;
run;
data table.zepopemp;
set table.zepopemp1968 table.zepopemp1975 table.zepopemp1982 table.zepopemp1990 table.zepopemp1999 table.zepopemp2007 table.zepopemp2012 ;
run;
data table.uucount;
set table.villecount_1968 table.villecount_1975 table.villecount_1982 table.villecount_1990 table.villecount_1999 table.villecount_2007 table.villecount_2012 ;
rename ville = uu99;
run;
data table.uucount;
set table.uucount;
if villepop<5000 then delete;
run;
data table.zecount;
set table.zecount_1968 table.zecount_1975 table.zecount_1982 table.zecount_1990 table.zecount_1999 table.zecount_2007 table.zecount_2012 ;
run;
data table.zecount;
set table.zecount;
if zepop<5000 then delete;
run;
proc datasets library = table;
delete Nipnaid_1968 Nipnaid_1975 Nipnaid_1982 Nipnaid_1990 Nipnaid_1999 Nipnaid_2007 Nipnaid_2012 
villecount_1968 villecount_1975 villecount_1982 villecount_1990 villecount_1999 villecount_2007 villecount_2012 
zecount_1968 zecount_1975 zecount_1982 zecount_1990 zecount_1999 zecount_2007 zecount_2012 
zepopemp1968 zepopemp1975 zepopemp1982 zepopemp1990 zepopemp1999 zepopemp2007 zepopemp2012 
villepopemp1968 villepopemp1975 villepopemp1982 villepopemp1990 villepopemp1999 villepopemp2007 villepopemp2012 
;
run;
quit;
data table.uu99;
set ccom.uu99;
run;
data table.cg12;
set ccom.cg12;
run;

/* Statistical secret : find cells with less than 5 observations */
data table.Nipnaid;
set table.Nipnaid;
sec=(count<=5);
run;
data table.uucount;
set table.uucount;
sec=(niml<=5);
run;
data table.zecount;
set table.zecount;
sec=(niml<=5);
run;
/* estimate the share eliminated */
proc freq data=table.Nipnaid;
by year;
weight count;
table sec;
run;
proc freq data=table.uucount;
by year;
weight niml;
table sec;
run;
proc freq data=table.zecount;
by year;
weight niml;
table sec;
run;
/* eliminate cells with less than 5 immigrants */
data table.Nipnaid;
set table.Nipnaid;
if sec then delete;
drop sec;
run;
data table.uucount;
set table.uucount;
if sec then delete;
drop sec;
run;
data table.zecount;
set table.zecount;
if sec then delete;
drop sec;
run;
/*
proc export data=table.zepopemp 
file="P:\Progs2015\JolePanel\TablesModif\RevisAout\zepopemp.dta" dbms=STATA
REPLACE ;
run;
proc export data=table.nipnaid 
file="P:\Progs2015\JolePanel\TablesModif\RevisAout\nipnaid.dta" dbms=STATA
REPLACE ;
run;
*/
